
const connection = require('../utils/myconn')
exports.getAllPost = (data, callback) => {
    var sql = `select posts.*,users.nickname,categories.name
    from posts
    join users on posts.user_id = users.id
    join categories on posts.category_id = categories.id
    where 1=1  `
    if (data.cate && Object.cate != 'all') {
        sql += ` and category_id = ${data.cate}`
    }
    if (data.status && data.status != 'all') {
        sql += ` and posts.status = '${data.status}'`
    }
    sql += ` order by id desc
    limit ${(data.pageNum - 1) * data.pageSize},${data.pageSize}`
    //调用方法获取数据
    connection.query(sql, (err, results) => {
        if (err) {
            callback(err)
        } else {
            // 再创建sql语句 进行总记录的查询
            sql = `select count(*) as cnt
                from posts
                join users on posts.user_id = users.id
                join categories on posts.category_id = categories.id where 1=1`

            if (data.cate && Object.cate != 'all') {
                sql += ` and category_id = ${data.cate}`
            }
            if (data.status && data.status != 'all') {
                sql += ` and posts.status = '${data.status}'`
            }
            connection.query(sql, (err2, res2) => {
                if (err2) {
                    callback(err2)
                } else {
                    // 没有错误，不仅仅要返回之前的查询数据，而且还要返回当前查询的总记录数
                    callback(null, { data: results, total: res2[0].cnt })
                }
            })

            // callback(err,results)
        }
    })
}


//文章新增 sql语句
exports.addPost = (obj,callback) =>{
    var sql = `insert into posts set ?`
    connection.query(sql,obj,(err,results) =>{
        // console.log(sql)
        if(err){
            callback(err)
        }else{
            callback(null)
        }
    })
}

exports.getPostById = (id,callback)=>{
    //根据id获取文章的数据
    var sql = `SELECT * FROM posts WHERE id = '${id}'`
    connection.query(sql,(err,results) =>{
        if(err){
            callback(err)
        }else{
            callback(null,results[0])
        }
    })
}

exports.editPostById = (obj,callback)=>{
    let sql = 'update posts set ? where id = ?'
    connection.query(sql,[obj,obj.id],(err,results)=>{
        if(err){
            callback(err)
        }else{
            callback(null)
        }
    })
}

exports.delPostById = (id,callback)=>{
    let sql = 'delete from posts where id = ?'
    connection.query(sql,[id],(err)=>{
        if(err){
            callback(err)
        }else{
            callback(null)
        }
    })
}